﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;

namespace DAL
{
    public partial class CAService : GetData
    {
        CRMEntities db;
        
        public List<UserEntity> LoadListCAByAll()
        {
            db = new CRMEntities();
            var query = (from us in db.Users
                         join re in db.Regions on us.Region_Code equals re.Region_Code
                         where us.Role == 3 && us.IsDisable == false
                         select new UserEntity
                         {
                             FirstName = us.FirstName,
                             LastName = us.LastName,
                             TelephoneNumber = us.TelephoneNumber,
                             Region_Code = us.Region_Code,

                         }).ToList();
            
            return query;
            
        }

        public UserEntity LoadCAById(int userid)
        {
            db = new CRMEntities();
            var query = (from us in db.Users
                         where us.User_ID == userid
                         select new UserEntity
                         {
                             FirstName = us.FirstName,
                             LastName = us.LastName,
                             TelephoneNumber = us.TelephoneNumber,
                             Region_Code = us.Region_Code,

                         }).SingleOrDefault();

            return query;
        }

        public List<CustomerEntity> LoadCurrentCallsByCAId(int CAid)
        {
            try
            {
                db = new CRMEntities();
                var query = from c in db.Customers
                            join d in db.DividingWorkings on c.Customer_ID equals d.Customer_ID
                            join a in db.Users on d.User_ID equals a.User_ID
                            join s in db.Sources on c.Source_ID equals s.Source_ID
                            where d.User_ID == CAid
                            select new DTO.CustomerEntity
                            {
                                Customer_ID = c.Customer_ID,
                                LastName = c.LastName,
                                FirstName = c.FirstName,
                                HomePhoneNumber = c.HomePhoneNumber,
                                OfficePhoneNumber = c.OfficePhoneNumber,
                                CurrentBrandName = c.CurrentBrandName,
                                CellPhoneNumber = c.CellPhoneNumber,
                                ImportDate = s.ImportDate,
                                EDD = c.EDD,
                                CallStatus = d.CallStatus_ID,
                            };
                return query.ToList();
            }
            catch
            {
                throw;
            }
            finally
            {
                db.Dispose();
            }
        }

        public void AssignCallsToCA(List<long> CustomersID, int CAid)
        {
            db = new CRMEntities();
            foreach (long id in CustomersID)
            {
                var selectedcus = (from cus in db.Customers
                          where cus.Customer_ID == id
                          select cus).SingleOrDefault();

                DividingWorking dw = new DividingWorking();
                dw.User_ID = CAid;
                dw.CallStatus_ID = (int)selectedcus.CallStatus;
                dw.Customer_ID = selectedcus.Customer_ID;

                db.DividingWorkings.Add(dw);
            }

            db.SaveChanges();
        }

        public List<CustomerEntity> LoadCustomerForDivide(int sourceID, int collectorID)
        {
            try
            {
                db = new CRMEntities();               
                var query = from c in db.Customers
                            where c.Collector_ID == collectorID
                            && c.Source_ID == sourceID
                            && (c.CallStatus == 5 || c.CallStatus == 2)
                            && c.CustomerStatus != 4
                            && !db.DividingWorkings.Any(a => a.Customer_ID == c.Customer_ID)
                            join r in db.Regions on c.Province_Code equals r.Region_Code
                            join s in db.Sources on c.Source_ID equals s.Source_ID
                            select new DTO.CustomerEntity
                            {
                                Customer_ID = c.Customer_ID,
                                LastName = c.LastName,
                                Collector_ID = c.Collector_ID,
                                Source_ID = c.Source_ID,
                                FirstName = c.FirstName,
                                HomePhoneNumber = c.HomePhoneNumber,
                                OfficePhoneNumber = c.OfficePhoneNumber,
                                PreviousBrandName = c.PreviousBrandName,
                                CurrentBrandName = c.CurrentBrandName,
                                CellPhoneNumber = c.CellPhoneNumber,
                                ImportDate = s.ImportDate,
                                Parent_Code = (int)r.Parent_Code,
                                EDD = c.EDD,
                                CallStatus = c.CallStatus,
                                CustomerStatus = c.CustomerStatus,
                            };
                return query.ToList();
            }
            catch
            {
                throw;
            }
            finally
            {
                db.Dispose();
            }
        }

        public List<vw_DWorking> GetDWorkingList()
        {
            try
            {
                string command = "SELECT  * FROM [CRM].[dbo].[vw_DWorkingAll]";
                db = new CRMEntities();                
                var query = db.Database.SqlQuery<vw_DWorking>(command);
                return query.Where(a=> a.CusCallStatus != null && a.CustomerStatus != 5 && a.CustomerStatus != 4 && a.CusCallStatus != 7).ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<DividingWorkingEntity> GetCallByCAId(int CAid)
        {
            db = new CRMEntities();
            var list = (from a in db.DividingWorkings
                        where a.User_ID == CAid
                        select new DividingWorkingEntity 
                        {
                            Customer_ID = a.Customer_ID,
                            User_ID = a.User_ID,
                            CallStatus_ID = a.CallStatus_ID,
                        });
            return list.ToList();
        }

        public void RemoveCallsByCAId(List<long> ListCustomerID, int CurrentCASelected)
        {
            db = new CRMEntities();
            List<DividingWorking> listDW = new List<DividingWorking>();
            foreach (long item in ListCustomerID)
            {
                DividingWorking dw = db.DividingWorkings.SingleOrDefault(a => a.Customer_ID == item && a.User_ID == CurrentCASelected);
                db.DividingWorkings.Remove(dw);
            }

            db.SaveChanges();
        }

        public void RemoveAllCallsByCAId(int CurrentCASelected)
        {
            db = new CRMEntities();
            List<DividingWorking> listDW = new List<DividingWorking>();

            var query = db.DividingWorkings.Where(a => a.User_ID == CurrentCASelected);
            foreach (DividingWorking i in query.ToList())
            {
                db.DividingWorkings.Remove(i);
            }

            db.SaveChanges();
        }

        public List<TaskListEntity> LoadCATaskList(int UserID)
        {
            db = new CRMEntities();
            var query = (from d in db.DividingWorkings.Where(a => a.User_ID == UserID)
                         join cu in db.Customers on d.Customer_ID equals cu.Customer_ID
                         join ca in db.CallStatus on d.CallStatus_ID equals ca.ID                         
                         orderby d.CallStatus_ID ascending
                         orderby cu.EDD ascending
                         select new TaskListEntity
                         {
                             CallStatus = ca.Name,
                             CallStatusID = d.CallStatus_ID,
                             CallEndTime = d.TimeEndCall,
                             CustomerName = cu.LastName + " " + cu.FirstName,
                             CellPhoneNumber = cu.CellPhoneNumber,
                             HomePhoneNumber = cu.HomePhoneNumber,
                             OfficePhoneNumber = cu.OfficePhoneNumber,
                             CustomerID = cu.Customer_ID,
                             UserID = d.User_ID,
                             EDD = cu.EDD,
                         }).ToList();
            return query;
        }

    }
}
